lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-10-14至2015-10-16(胡工)/2015-10-16.sql

update tb_ErpSystemCategory set Sc_ClassName='一级' where Sc_ClassCode='BEBBBCADDEBFJDFFC'
update tb_ErpSystemCategory set Sc_ClassName='二级' where Sc_ClassCode='BEBBBCADEGBGAFFJC'
update tb_ErpSystemCategory set Sc_ClassName='三级' where Sc_ClassCode='BEBBBCADAFBHBCHCI'


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_DressSaleRentalDetail')
BEGIN
	DROP VIEW [dbo].View_DressSaleRentalDetail
END 
GO
create View View_DressSaleRentalDetail
as
SELECT   tb_ErpDressSaleRentalDetail.Id,Dsrd_Number,Dsrd_DressNumber,Dsrd_DressName,Dsrd_SaleRentalQuantity,Dsrd_CostPrice,
Dsrd_SalePrice,Dsrd_BackTime,Dsrd_BackStatus,Dsrd_BackRemark,Dsrd_CreateDateTime,Dsrd_CreateName,Dsrd_UpdateDateTime,
Dsrd_UpdateName,Dsro_Number,Dsro_Type,Dsro_Amount,Dsro_MortgageAmount,Dsro_CustomerNumber,Dsro_TakeDressTime,
Dsro_ReservationBackTime,Dsro_Remark,Dsro_CreateDateTime,Dsro_CreateName,Dsro_UpdateDateTime,Dsro_UpdateName,Cus_Name ,
[dbo].[fn_GetAlsoClothesDays]('AlsoClothesDay') as '还衣天数',
DateAdd(d, -CONVERT(int,[dbo].[fn_GetAlsoClothesDays]('AlsoClothesDay')),Dsro_TakeDressTime) as '还衣天数取衣日期',
DateAdd(d, CONVERT(int,[dbo].[fn_GetAlsoClothesDays]('AlsoClothesDay')),Dsro_ReservationBackTime) as '还衣天数加还衣日期',
--(select  max(DateAdd(d,CONVERT(int,[dbo].[fn_GetAlsoClothesDays]('AlsoClothesDay')),Dsro_ReservationBackTime)) from tb_ErpDressSaleRentalDetail where Dsrd_DressNumber=Dsrd_DressNumber) as '最大还衣时间',
--(select  min(DateAdd(d,CONVERT(int,[dbo].[fn_GetAlsoClothesDays]('AlsoClothesDay')),Dsro_ReservationBackTime)) from tb_ErpDressSaleRentalDetail where Dsrd_DressNumber=Dsrd_DressNumber) as '最小还衣时间'
Dsfm_RentPrice
FROM tb_ErpDressSaleRentalDetail left join tb_ErpDressSaleRentalOrder on Dsrd_Number=Dsro_Number  
left join tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber 
left join tb_ErpDressFrom on Dsrd_DressNumber=Dsfm_DressNumber
group by tb_ErpDressSaleRentalDetail.Id,Dsrd_Number,Dsrd_DressNumber,Dsrd_DressName,Dsrd_SaleRentalQuantity,Dsrd_CostPrice,
Dsrd_SalePrice,Dsrd_BackTime,Dsrd_BackStatus,Dsrd_BackRemark,Dsrd_CreateDateTime,Dsrd_CreateName,Dsrd_UpdateDateTime,
Dsrd_UpdateName,Dsro_Number,Dsro_Type,Dsro_Amount,Dsro_MortgageAmount,Dsro_CustomerNumber,Dsro_TakeDressTime,
Dsro_ReservationBackTime,Dsro_Remark,Dsro_CreateDateTime,Dsro_CreateName,Dsro_UpdateDateTime,Dsro_UpdateName,Cus_Name ,Dsfm_RentPrice
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetAlsoClothesDays]') and xtype in (N'FN', N'IF', N'TF'))
BEGIN
	DROP FUNCTION [dbo].fn_GetAlsoClothesDays
END 
GO

CREATE function [dbo].[fn_GetAlsoClothesDays](@FID varchar(800))
/****** 
获取还衣天数
******/

Returns varchar(800)
As
Begin
	Declare @Num int
	begin
		--串连数据
		select @Num=Sconfig_Value from tb_ErpSystemConfigure where Sconfig_Code=@FID
    end 	    
	Return  @Num
End
GO





IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpOtherIncomeAndExpenses')
BEGIN
	DROP VIEW [dbo].View_ErpOtherIncomeAndExpenses
END 
GO
create View View_ErpOtherIncomeAndExpenses
as
SELECT   dbo.tb_ErpOtherIncomeAndExpenses.ID, 
dbo.tb_ErpOtherIncomeAndExpenses.Oiae_Type, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_ProjectName,
				 dbo.tb_ErpOtherIncomeAndExpenses.Oiae_Money, 
                  Oiae_IEDatetime,
				  dbo.tb_ErpOtherIncomeAndExpenses.Oiae_PersonHandling, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_ThePayer, dbo.tb_ErpOtherIncomeAndExpenses.Oiae_Invoice, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_Remark, dbo.tb_ErpOtherIncomeAndExpenses.Oiae_CreateDatetime, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_FinancialAuditState, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_FinancialAuditPeople, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_ManagerAuditState, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_ManagerAuditPeople, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_CEOAuditState, dbo.tb_ErpOtherIncomeAndExpenses.Oiae_CEOAuditPeople, 
                dbo.tb_ErpOtherIncomeAndExpenses.Oiae_PaymentMethod,
				dbo.fn_CheckUserIDGetUserName(Oiae_ThePayer) AS [User_Name],
				dbo.fn_CheckUserIDGetUserName(Oiae_PersonHandling) AS Oiae_PersonHandlingName,
				dbo.fn_CheckUserIDGetUserName(Oiae_FinancialAuditPeople) AS Oiae_FinancialAuditPeopleName,
				dbo.fn_CheckUserIDGetUserName(Oiae_ManagerAuditPeople) AS Oiae_ManagerAuditPeopleName,
				dbo.fn_CheckUserIDGetUserName(Oiae_CEOAuditPeople) AS Oiae_CEOAuditPeopleName,
				dbo.fn_GetClassCodeToName(Oiae_PaymentMethod,Oiae_PaymentMethod) AS Oiae_PaymentMethodName

FROM      dbo.tb_ErpOtherIncomeAndExpenses

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWagePaymentRecords
END 
GO
create View Vw_OrdersWagePaymentRecords
as
SELECT   
tb_ErpPayment.ID, 
Pay_OrdNumber,
Pay_AmountOf,
Pay_OpenSingle, 
Pay_ThePayee,
Pay_PaymentMethod,
Pay_OrdersLocation,
Pay_ReceivableProject,
Pay_Remark, 
Pay_CreateDatetime,
Pay_Category,
Pay_TwoPinsCategory, 
dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS Pay_UserName, 
dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS Pay_ThePayeeName, 
dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS Pay_PaymentMethodName, 
dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS Pay_TwoPinsCategoryName, 
Pay_FinancialAuditdPeople,
Pay_FinancialAudit,
dbo.fn_CheckUserIDGetUserName(Pay_FinancialAuditdPeople)AS Pay_FinancialAuditdPeopleName, 
Pay_ShootingName,
Pay_Type,
Ord_DividedShop,
Ord_Type,
Cus_Name as Ord_CustomerName1,
(select Tsorder_Name from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name,
(select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName,
(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name,
Ord_CreateDatetime,
Ord_SeriesName,
Ord_PhotographyCategory
,Ord_SinceOrderNumber
FROM    tb_ErpPayment
left join Vw_Customer_PaymentOrders on Pay_OrdNumber=Ord_Number


GO








 if not exists
(select * from syscolumns where id=object_id('tb_ErpDressRefundRecord') and name='DRR_HandledName') 
begin
alter table tb_ErpDressRefundRecord add DRR_HandledName nvarchar(20) 
end


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_MonthlyReport')
BEGIN
	DROP VIEW [dbo].Vw_MonthlyReport
END 
GO
create View Vw_MonthlyReport
as
select 
Pay_CreateDatetimes
,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款')  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID')  and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOrdersIncome
, (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款'  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayReplenishmentIncome
 ,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款'  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayLateStageIncome
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherIncome
,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where Mcrr_CreateDatetime>=Pay_CreateDatetimes and Mcrr_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberIncome
,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and Ord_CreateDatetime>=Pay_CreateDatetimes and Ord_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayEarlyPerformance
,(select sum(Plu_Amount) from tb_ErpPlusPickItems where Plu_CreateTime>=Pay_CreateDatetimes and Plu_CreateTime<=Pay_CreateDatetimes+' 23:59:59.000') as  DayPluslatepickPerformance 
,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where Tsorder_CreateDatetime>=Pay_CreateDatetimes and Tsorder_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherPerformance
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='支出')  and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='财务支出')  and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure2
,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where Mcpt_CreateDatetime>=Pay_CreateDatetimes and Mcpt_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberCardPaymentIncome
,(select sum(Mcpm_BackAoumnt) from tb_ErpMemberCardPhotographerMain where (Mcpm_BackStatus='0')  and Mcpm_CreateDatetime>=Pay_CreateDatetimes and Mcpm_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as 摄友会收入
 from 
 (
  select Pay_CreateDatetimes from  Vw_ReportTime
 ) as MonthlyReport
group by Pay_CreateDatetimes


GO